package dao.impl;

import dao.GoodsClassDao;
import pojo.Goods;
import pojo.GoodsClass;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 实现商品分类增删改查的实现类
 * @author 周超
 */
public class GoodsClassDaoImpl implements GoodsClassDao {

    /**
     * 新增商品分类
     * @param goodsClass
     * @return
     */
    @Override
    public int addGoodsClass(GoodsClass goodsClass) {
        return BaseDao.cud("insert into goodsclass values(sequence_goodsclass.nextval,?,?,?)",
                goodsClass.getClName(),goodsClass.getFaId(), goodsClass.getClDescribe());

    }

    /**
     * 删除商品分类
     * @param clId
     * @return
     */
    @Override
    public int delGoodsClass(int clId) {
        return BaseDao.cud("delete from goodsclass where cl_id="+clId);
    }

    /**
     * 修改商品分类信息
     * @param goodsClass
     * @return
     */
    @Override
    public int altGoodsClass(GoodsClass goodsClass) {
        return BaseDao.cud("update goodsClass set cl_name=?,fa_Id=?," +
                        "cl_describe=? where cl_id=?",goodsClass.getClName(),
                goodsClass.getFaId(),goodsClass.getClDescribe(),goodsClass.getClId());
    }

    /**
     * 查询所有商品分类信息
     * @return
     */
    @Override
    public List<GoodsClass> queryAll() {
        List<GoodsClass> list=new ArrayList<GoodsClass>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from goodsclass";
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                GoodsClass goodsClass=new GoodsClass();
                goodsClass.setClId(rs.getInt("cl_id"));
                goodsClass.setClName(rs.getString("cl_name"));
                goodsClass.setFaId(rs.getInt("fa_id"));
                goodsClass.setClDescribe(rs.getString("cl_describe"));
                list.add(goodsClass);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return list;
    }

    /**
     * 根据商品分类名称查询商品分类id
     * @return
     */
    @Override
    public int queryGoodsClassId(String clName) {
        int id=0;
        GoodsClass goodsClass=new GoodsClass();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from goodsclass where cl_name="+clName;
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                goodsClass.setClId(rs.getInt("cl_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return goodsClass.getClId();
    }

    /**
     * 按条件模糊查询所有商品分类信息
     * @param pb
     * @param className
     * @return
     */
    @Override
    public PageBean queryAllGoodsClassInfo(PageBean pb, String className) {
        List<GoodsClass> list=new ArrayList<GoodsClass>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String s=null;
        if(null!=className&&""!=className){
            s=" 1=1 and cl_name like '%"+className+"%' and ";
        }
        else{
            s="";
        }
        String sql=" select * from (\n" +
                "    select rownum r,c.* from goodsclass c where "+s+" rownum<=?\n" +
                "    )where r>?";
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            ps.setInt(1,pb.getPageSize()*pb.getPageIndex());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while(rs.next()){
                GoodsClass goodsClass=new GoodsClass();
                goodsClass.setClId(rs.getInt("cl_id"));
                goodsClass.setClName(rs.getString("cl_name"));
                goodsClass.setFaId(rs.getInt("fa_id"));
                goodsClass.setClDescribe(rs.getString("cl_describe"));
                list.add(goodsClass);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        pb.setList(list);
        return pb;
    }

    /**
     * 模糊查询数据库中有多少条符合条件的商品分类数据
     * @param name
     * @return
     */
    @Override
    public int count(String name) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String s=null;
        if(null!=name&&""!=name){
            s=" 1=1 and cl_name like '%"+name+"%'";
        }
        else{
            s=" 1=1";
        }
        String sql="select count(*) from goodsclass where "+ s;
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return count;
    }

    /**
     * 根据商品分类id查询商品分类信息
     * @param clId
     * @return
     */
    @Override
    public GoodsClass queryGoodsClassInfo(int clId) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from goodsclass where cl_id="+clId;
        conn=BaseDao.getConnection();
        GoodsClass goodsClass=new GoodsClass();
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){

                goodsClass.setClId(rs.getInt("cl_id"));
                goodsClass.setClName(rs.getString("cl_name"));
                goodsClass.setFaId(rs.getInt("fa_id"));
                goodsClass.setClDescribe(rs.getString("cl_describe"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return goodsClass;
    }
}
